import sqlite3 as db
import os
import lib.MySequence

class InitDB(MySequence):
    """docstring for InitDB
初始化数据库
    """
    def __init__(self, path):
        super(InitDB, self).__init__()
        
    def createMyTables(self):
        cu= self.cu
        conn = self.conn
        sql_createTable_bank="""
    create table bank (
       id                   INT4                PRIMARY KEY  not null,
       name                 vchar(90)            not null,
       cardNum              vchar(30)            not null,
       isCredit             INT4                 not null,
       balance              FLOAT8               not null,
       creditBalance        FLOAT8               null,
       interestFree         INT4                 ,
       pic                  blob                 ,
       isDeleted            int                  not null
    )
    """
        sql_createTbale_channel= """
    create table channel (
       id                   INT4               PRIMARY KEY  not null,
       level                int                  not null,
       channelNameP         vchar(255)           not null,
       channelNameS         vchar(255)           not null,
       isSelf               bool                 not null,
       type                 int                  not null,
       tax                  FLOAT4               not null,
       bankNum              vchar(30)            ,
       pic                  blob                 ,
       isDeleted            int                  not null
    )
    """
        sql_crateTable_accountBook = """
    create table accountBook (
       id                   INT2              PRIMARY KEY   not null,
       date                 DATE                 not null,
       bankNum              vchar(30)            not null,
       channeName           vchar(255)           not null,
       amount               float                not null,
       booked               int                  not null,
       bookedDate           date                 not null,
       isExpense            int                  not null,
       commands             clob                 
    )
    """
        try:
            self.createSequence('bank_seq','bank')
        except Exception as ex:
            raise ex
            print("出错啦 %s" % ex)
        try:
            cu.execute(sql_createTable_bank)
            cu.execute(sql_createTbale_channel)
            cu.execute(sql_crateTable_accountBook)
            conn.commit()
        except db.OperationalError as ex:
            print("出错啦 %s" % ex)
            return 0
        return 1

    def  mySequenceNextValue(self,seqName):
        cu = self.cu
        getValueSql = 'select step, value from mySequence where name = ?'
        updateValueSql = 'update mySequence set value = :value where name = :name'
        step, nextValue = cu.execute(getValueSql, seqName)
        nextValue += step
        cu.execute(updateValueSql, {'name':seqName, 'value':value})
        self.conn.commit()
        return nextValue

    def newDatabase(self):
        conn = self.conn
        if self.createMySequenceTable():
            print("建立sequence成功")
        else:
            print("建立sequence失败")
        if self.createMyTables():
            print("建立 tables 成功")
        else:
            print("建立 tables 失败")
        conn.commit()
        conn.close()





